<!DOCTYPE html><html><head><title>MySQL学习笔记（Day014：触发器下/存储过程/自定义函数）</title><meta charset='utf-8'><link href='https://dn-maxiang.qbox.me/res-min/themes/marxico.css' rel='stylesheet'><style>

.note-content  {font-family: 'Helvetica Neue', Arial, 'Hiragino Sans GB', STHeiti, 'Microsoft YaHei', 'WenQuanYi Micro Hei', SimSun, Song, sans-serif;}
</style></head><body><div id='preview-contents' class='note-content'>
                        <div id="wmd-preview" class="preview-content"></div>
                    <div id="wmd-preview-section-1" class="wmd-preview-section preview-content">

</div><div id="wmd-preview-section-2" class="wmd-preview-section preview-content">

<h1 id="mysql学习笔记day014触发器下存储过程自定义函数">MySQL学习笔记（Day014：触发器下/存储过程/自定义函数）</h1>

<p><p class="note-tags "><code class="notebook">MySQL学习</code> </p></p>

<div><div class="toc"><div class="toc">
<ul>
<li><a href="#mysql学习笔记day014触发器下存储过程自定义函数">MySQL学习笔记（Day014：触发器下/存储过程/自定义函数）</a><ul>
<li><a href="#一-作业讲解">一. 作业讲解</a></li>
<li><a href="#二-触发器-下">二. 触发器 ・ 下</a><ul>
<li><a href="#1-触发器总结">1. 触发器总结</a></li>
<li><a href="#2-触发器模拟物化视图">2. 触发器模拟物化视图</a></li>
</ul>
</li>
<li><a href="#三-存储过程">三. 存储过程</a><ul>
<li><a href="#1-存储过程介绍">1. 存储过程介绍</a></li>
<li><a href="#2-存储过程举例与流程控制语句">2. 存储过程举例与流程控制语句</a></li>
</ul>
</li>
<li><a href="#三-自定义函数">三. 自定义函数</a></li>
</ul>
</li>
</ul>
</div>
</div>
</div>

</div><div id="wmd-preview-section-3" class="wmd-preview-section preview-content">

<h2 id="一-作业讲解">一. 作业讲解</h2>

<ul><li><p><strong>查询employees表中非基层用户的最近详细信息</strong></p>

<blockquote>
  <p><strong>关于<code>Group By</code>在《SQL必知必会》中提及的部分规定：</strong></p>
  
  <ol>
  <li rel="1"><code>GROUP BY</code>子句中列出的每一列都必须是检索列或有效的表达式（但不能是聚集函数）。如果在SELECT中使用表达式，则必须在<code>GROUP BY</code>子句中指定相同的表达式。不能使用别名。</li>
  <li rel="2">除聚集计算语句外，<code>SELECT语句中的每一列都必须在GROUP BY子句中给出</code>。</li></ol>
</blockquote></li>
</ul>

</div><div id="wmd-preview-section-4" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs"><span class="hljs-comment line-number">1.</span><span class="hljs-operator"><span class="hljs-keyword">SELECT</span> <br><span class="hljs-comment line-number">2.</span>    <span class="hljs-keyword">e</span>.emp_no,<br><span class="hljs-comment line-number">3.</span>    <span class="hljs-keyword">CONCAT</span>(last_name, <span class="hljs-string">' '</span>, first_name) <span class="hljs-keyword">AS</span> <span class="hljs-keyword">name</span>,<br><span class="hljs-comment line-number">4.</span>    <span class="hljs-keyword">t</span>.title,<br><span class="hljs-comment line-number">5.</span>    dp.dept_name,<br><span class="hljs-comment line-number">6.</span>    s.salary<br><span class="hljs-comment line-number">7.</span><span class="hljs-keyword">FROM</span><br><span class="hljs-comment line-number">8.</span>    employees <span class="hljs-keyword">e</span><br><span class="hljs-comment line-number">9.</span>        <span class="hljs-keyword">LEFT</span> <span class="hljs-keyword">JOIN</span><br><span class="hljs-comment line-number">10.</span>    dept_manager <span class="hljs-keyword">d</span> <span class="hljs-keyword">ON</span> <span class="hljs-keyword">e</span>.emp_no = <span class="hljs-keyword">d</span>.emp_no<br><span class="hljs-comment line-number">11.</span>        <span class="hljs-keyword">LEFT</span> <span class="hljs-keyword">JOIN</span><br><span class="hljs-comment line-number">12.</span>    (<span class="hljs-keyword">SELECT</span> <br><span class="hljs-comment line-number">13.</span>        emp_no, title, from_date, <span class="hljs-keyword">to_date</span><br><span class="hljs-comment line-number">14.</span>    <span class="hljs-keyword">FROM</span><br><span class="hljs-comment line-number">15.</span>        titles<br><span class="hljs-comment line-number">16.</span>    <span class="hljs-keyword">WHERE</span><br><span class="hljs-comment line-number">17.</span>        (emp_no , from_date, <span class="hljs-keyword">to_date</span>) <span class="hljs-keyword">IN</span> (<span class="hljs-keyword">SELECT</span> <br><span class="hljs-comment line-number">18.</span>                emp_no, <span class="hljs-keyword">MAX</span>(from_date), <span class="hljs-keyword">MAX</span>(<span class="hljs-keyword">to_date</span>)<br><span class="hljs-comment line-number">19.</span>            <span class="hljs-keyword">FROM</span><br><span class="hljs-comment line-number">20.</span>                titles <span class="hljs-keyword">AS</span> b<br><span class="hljs-comment line-number">21.</span>            <span class="hljs-keyword">GROUP</span> <span class="hljs-keyword">BY</span> b.emp_no)) <span class="hljs-keyword">t</span> <span class="hljs-keyword">ON</span> <span class="hljs-keyword">t</span>.emp_no = <span class="hljs-keyword">e</span>.emp_no<br><span class="hljs-comment line-number">22.</span>        <span class="hljs-keyword">LEFT</span> <span class="hljs-keyword">JOIN</span><br><span class="hljs-comment line-number">23.</span>    (<span class="hljs-keyword">SELECT</span> <br><span class="hljs-comment line-number">24.</span>        dept_no, emp_no, from_date, <span class="hljs-keyword">to_date</span><br><span class="hljs-comment line-number">25.</span>    <span class="hljs-keyword">FROM</span><br><span class="hljs-comment line-number">26.</span>        dept_emp<br><span class="hljs-comment line-number">27.</span>    <span class="hljs-keyword">WHERE</span><br><span class="hljs-comment line-number">28.</span>        (emp_no , from_date, <span class="hljs-keyword">to_date</span>) <span class="hljs-keyword">IN</span> (<span class="hljs-keyword">SELECT</span> <br><span class="hljs-comment line-number">29.</span>                emp_no, <span class="hljs-keyword">MAX</span>(from_date), <span class="hljs-keyword">MAX</span>(<span class="hljs-keyword">to_date</span>)<br><span class="hljs-comment line-number">30.</span>            <span class="hljs-keyword">FROM</span><br><span class="hljs-comment line-number">31.</span>                dept_emp <span class="hljs-keyword">AS</span> b<br><span class="hljs-comment line-number">32.</span>            <span class="hljs-keyword">GROUP</span> <span class="hljs-keyword">BY</span> b.emp_no)) de <span class="hljs-keyword">ON</span> de.emp_no = <span class="hljs-keyword">e</span>.emp_no<br><span class="hljs-comment line-number">33.</span>        <span class="hljs-keyword">LEFT</span> <span class="hljs-keyword">JOIN</span><br><span class="hljs-comment line-number">34.</span>    (<span class="hljs-keyword">SELECT</span> <br><span class="hljs-comment line-number">35.</span>        emp_no, salary, from_date, <span class="hljs-keyword">to_date</span><br><span class="hljs-comment line-number">36.</span>    <span class="hljs-keyword">FROM</span><br><span class="hljs-comment line-number">37.</span>        salaries<br><span class="hljs-comment line-number">38.</span>    <span class="hljs-keyword">WHERE</span><br><span class="hljs-comment line-number">39.</span>        (emp_no , from_date, <span class="hljs-keyword">to_date</span>) <span class="hljs-keyword">IN</span> (<span class="hljs-keyword">SELECT</span> <br><span class="hljs-comment line-number">40.</span>                emp_no, <span class="hljs-keyword">MAX</span>(from_date), <span class="hljs-keyword">MAX</span>(<span class="hljs-keyword">to_date</span>)<br><span class="hljs-comment line-number">41.</span>            <span class="hljs-keyword">FROM</span><br><span class="hljs-comment line-number">42.</span>                salaries <span class="hljs-keyword">AS</span> b<br><span class="hljs-comment line-number">43.</span>            <span class="hljs-keyword">GROUP</span> <span class="hljs-keyword">BY</span> b.emp_no)) s <span class="hljs-keyword">ON</span> s.emp_no = <span class="hljs-keyword">e</span>.emp_no<br><span class="hljs-comment line-number">44.</span>        <span class="hljs-keyword">LEFT</span> <span class="hljs-keyword">JOIN</span><br><span class="hljs-comment line-number">45.</span>    departments dp <span class="hljs-keyword">ON</span> dp.dept_no = de.dept_no<br><span class="hljs-comment line-number">46.</span><span class="hljs-keyword">WHERE</span><br><span class="hljs-comment line-number">47.</span>    <span class="hljs-keyword">d</span>.emp_no <span class="hljs-keyword">IS</span> <span class="hljs-literal">NULL</span>;</span><br></code></pre>

</div><div id="wmd-preview-section-5" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs"><span class="hljs-comment line-number">1.</span><span class="hljs-comment">--</span><br><span class="hljs-comment line-number">2.</span><span class="hljs-comment">-- 改进的子查询语句 - 1</span><br><span class="hljs-comment line-number">3.</span><span class="hljs-comment">--</span><br><span class="hljs-comment line-number">4.</span><span class="hljs-operator"><span class="hljs-keyword">SELECT</span> <br><span class="hljs-comment line-number">5.</span>    emp_no, title, from_date, <span class="hljs-keyword">to_date</span><br><span class="hljs-comment line-number">6.</span><span class="hljs-keyword">FROM</span><br><span class="hljs-comment line-number">7.</span>    titles<br><span class="hljs-comment line-number">8.</span>        <span class="hljs-keyword">WHERE</span><br><span class="hljs-comment line-number">9.</span>        (emp_no , from_date, <span class="hljs-keyword">to_date</span>) <span class="hljs-keyword">IN</span> <br><span class="hljs-comment line-number">10.</span>            (<br><span class="hljs-comment line-number">11.</span>                <span class="hljs-keyword">SELECT</span> <br><span class="hljs-comment line-number">12.</span>                    emp_no, <span class="hljs-keyword">MAX</span>(from_date), <span class="hljs-keyword">MAX</span>(<span class="hljs-keyword">to_date</span>)  <span class="hljs-comment">-- 因为数据本身的问题，这里from_date和to_date都要</span><br><span class="hljs-comment line-number">13.</span>                <span class="hljs-keyword">FROM</span><br><span class="hljs-comment line-number">14.</span>                    titles <span class="hljs-keyword">AS</span> b<br><span class="hljs-comment line-number">15.</span>                    <span class="hljs-keyword">GROUP</span> <span class="hljs-keyword">BY</span> b.emp_no<br><span class="hljs-comment line-number">16.</span>            ) <span class="hljs-comment">-- 这个子查询表示以emp_no分类，找到最大（最近）的from_date和to_date</span><br><span class="hljs-comment line-number">17.</span>              <span class="hljs-comment">-- 而where条件在这个最大的基础上，过滤出我们要的title。(salary同理)</span><br><span class="hljs-comment line-number">18.</span><br><span class="hljs-comment line-number">19.</span><br><span class="hljs-comment line-number">20.</span><span class="hljs-comment">--</span><br><span class="hljs-comment line-number">21.</span><span class="hljs-comment">-- 改进的子查询语句 - 2</span><br><span class="hljs-comment line-number">22.</span><span class="hljs-comment">--</span><br><span class="hljs-comment line-number">23.</span><span class="hljs-keyword">SELECT</span> <br><span class="hljs-comment line-number">24.</span>    emp_no, title, from_date, <span class="hljs-keyword">to_date</span><br><span class="hljs-comment line-number">25.</span><span class="hljs-keyword">FROM</span><br><span class="hljs-comment line-number">26.</span>    titles <span class="hljs-keyword">AS</span> a<br><span class="hljs-comment line-number">27.</span><span class="hljs-keyword">WHERE</span><br><span class="hljs-comment line-number">28.</span>    (from_date, <span class="hljs-keyword">to_date</span>) = (<span class="hljs-keyword">SELECT</span> <br><span class="hljs-comment line-number">29.</span>            <span class="hljs-keyword">MAX</span>(from_date), <span class="hljs-keyword">MAX</span>(<span class="hljs-keyword">to_date</span>)  <span class="hljs-comment">-- 同样使用from_date和to_date</span><br><span class="hljs-comment line-number">30.</span>        <span class="hljs-keyword">FROM</span><br><span class="hljs-comment line-number">31.</span>            titles <span class="hljs-keyword">AS</span> b<br><span class="hljs-comment line-number">32.</span>        <span class="hljs-keyword">WHERE</span><br><span class="hljs-comment line-number">33.</span>            a.emp_no = b.emp_no  <span class="hljs-comment">-- 这个是一个关联子查询</span><br><span class="hljs-comment line-number">34.</span>        <span class="hljs-keyword">GROUP</span> <span class="hljs-keyword">BY</span> b.emp_no);</span><br></code></pre>

<ul><li><strong>Rank排名一条SQL语句</strong></li>
</ul>

</div><div id="wmd-preview-section-6" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs"><span class="hljs-comment line-number">1.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> test_rank_2;</span><br><span class="hljs-comment line-number">2.</span>+<span class="hljs-comment">------+-------+</span><br><span class="hljs-comment line-number">3.</span>| id   | score |<br><span class="hljs-comment line-number">4.</span>+<span class="hljs-comment">------+-------+</span><br><span class="hljs-comment line-number">5.</span>|    1 |    10 |<br><span class="hljs-comment line-number">6.</span>|    2 |    20 |<br><span class="hljs-comment line-number">7.</span>|    3 |    30 |<br><span class="hljs-comment line-number">8.</span>|    4 |    30 |<br><span class="hljs-comment line-number">9.</span>|    5 |    40 |<br><span class="hljs-comment line-number">10.</span>|    6 |    40 |<br><span class="hljs-comment line-number">11.</span>+<span class="hljs-comment">------+-------+</span><br><span class="hljs-comment line-number">12.</span>6 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">13.</span><br><span class="hljs-comment line-number">14.</span>mysql&gt; <span class="hljs-keyword">select</span> <span class="hljs-keyword">id</span>, score,<br><span class="hljs-comment line-number">15.</span>    -&gt; <span class="hljs-keyword">case</span><br><span class="hljs-comment line-number">16.</span>    -&gt;     <span class="hljs-keyword">when</span> @prev_value = score <span class="hljs-keyword">then</span> @rank_count<br><span class="hljs-comment line-number">17.</span>    -&gt;     <span class="hljs-keyword">when</span> @prev_value := score <span class="hljs-keyword">then</span> @rank_count := @rank_count + <span class="hljs-number">1</span><br><span class="hljs-comment line-number">18.</span>    -&gt; <span class="hljs-keyword">end</span> <span class="hljs-keyword">as</span> rank_column<br><span class="hljs-comment line-number">19.</span>    -&gt; <span class="hljs-keyword">from</span> test_rank_2, (<span class="hljs-keyword">select</span> @prev_value:=<span class="hljs-literal">NULL</span>, @rank_count:=<span class="hljs-number">0</span>) <span class="hljs-keyword">as</span> <span class="hljs-keyword">t</span>  <span class="hljs-comment">-- 和RowNumber思路一样，增加一个表</span><br><span class="hljs-comment line-number">20.</span>    -&gt; <span class="hljs-keyword">order</span> <span class="hljs-keyword">by</span> score <span class="hljs-keyword">desc</span>;</span><br><span class="hljs-comment line-number">21.</span>+<span class="hljs-comment">------+-------+-------------+</span><br><span class="hljs-comment line-number">22.</span>| id   | score | rank_column |<br><span class="hljs-comment line-number">23.</span>+<span class="hljs-comment">------+-------+-------------+</span><br><span class="hljs-comment line-number">24.</span>|    5 |    40 | 1           |<br><span class="hljs-comment line-number">25.</span>|    6 |    40 | 1           |<br><span class="hljs-comment line-number">26.</span>|    3 |    30 | 2           |<br><span class="hljs-comment line-number">27.</span>|    4 |    30 | 2           |<br><span class="hljs-comment line-number">28.</span>|    2 |    20 | 3           |<br><span class="hljs-comment line-number">29.</span>|    1 |    10 | 4           |<br><span class="hljs-comment line-number">30.</span>+<span class="hljs-comment">------+-------+-------------+</span><br><span class="hljs-comment line-number">31.</span>6 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)</span><br></code></pre>

<hr>

</div><div id="wmd-preview-section-7" class="wmd-preview-section preview-content">

<h2 id="二-触发器-下">二. 触发器 ・ 下</h2>

</div><div id="wmd-preview-section-8" class="wmd-preview-section preview-content">

<h3 id="1-触发器总结">1. 触发器总结</h3>

<ul><li>触发器对性能有损耗，应当非常慎重使用；</li>
<li>对于事物表，<code>触发器执行失败则整个语句回滚</code>；</li>
<li>Row格式主从复制，<code>触发器不会在从库上执行</code>；  <br>
<ul>
<li>因为从库复制的肯定是主库已经提交的数据，既然已经提交了说明触发器已经被触发过了，所以从库不会执行。</li></ul></li>
<li><p>使用触发器时应防止递归执行；</p>

<pre class="prettyprint hljs-dark"><code class="language-sql hljs"><span class="hljs-comment line-number">1.</span>delimiter //<br><span class="hljs-comment line-number">2.</span><span class="hljs-operator"><span class="hljs-keyword">create</span> <span class="hljs-keyword">trigger</span> trg_test<br><span class="hljs-comment line-number">3.</span>    <span class="hljs-keyword">before</span> <span class="hljs-keyword">update</span> <span class="hljs-keyword">on</span> <span class="hljs-string">'test_trigger'</span><br><span class="hljs-comment line-number">4.</span>    <span class="hljs-keyword">for</span> <span class="hljs-keyword">each</span> <span class="hljs-keyword">row</span><br><span class="hljs-comment line-number">5.</span><span class="hljs-keyword">begin</span><br><span class="hljs-comment line-number">6.</span>    <span class="hljs-keyword">update</span> test_trigger <span class="hljs-keyword">set</span> score=<span class="hljs-number">20</span> <span class="hljs-keyword">where</span> <span class="hljs-keyword">name</span> = <span class="hljs-keyword">old</span>.<span class="hljs-keyword">name</span>;</span>  <span class="hljs-comment">-- 又触发了update操作，循环触发了</span><br><span class="hljs-comment line-number">7.</span><span class="hljs-operator"><span class="hljs-keyword">end</span>;</span>//<br></code></pre></li>
</ul>

</div><div id="wmd-preview-section-9" class="wmd-preview-section preview-content">

<h3 id="2-触发器模拟物化视图">2. 触发器模拟物化视图</h3>

<ul><li><p><strong>物化视图的概念</strong></p>

<ul>
<li>不是基于基表的虚表</li>
<li>根据基表实际存在的实表</li>
<li>预先计算并保存耗时较多的SQL操作结果（如多表链接(join)或者group by等）</li></ul></li>
<li><p><strong>模拟物化视图</strong></p></li>
</ul>

</div><div id="wmd-preview-section-10" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs"><span class="hljs-comment line-number">1.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> Orders  <br><span class="hljs-comment line-number">2.</span>    -&gt; (order_id <span class="hljs-built_in">int</span> <span class="hljs-keyword">unsigned</span> <span class="hljs-keyword">not</span> <span class="hljs-literal">null</span> auto_increment,<br><span class="hljs-comment line-number">3.</span>    -&gt; product_name <span class="hljs-built_in">varchar</span>(<span class="hljs-number">30</span>) <span class="hljs-keyword">not</span> <span class="hljs-literal">null</span>,<br><span class="hljs-comment line-number">4.</span>    -&gt; price <span class="hljs-built_in">decimal</span>(<span class="hljs-number">8</span>,<span class="hljs-number">2</span>) <span class="hljs-keyword">not</span> <span class="hljs-literal">null</span>,<br><span class="hljs-comment line-number">5.</span>    -&gt; amount <span class="hljs-built_in">smallint</span> <span class="hljs-keyword">not</span> <span class="hljs-literal">null</span>,<br><span class="hljs-comment line-number">6.</span>    -&gt; primary <span class="hljs-keyword">key</span>(order_id));</span><br><span class="hljs-comment line-number">7.</span>Query OK, 0 rows affected (0.13 sec)  <span class="hljs-comment">-- 创建Orders表</span><br><span class="hljs-comment line-number">8.</span><br><span class="hljs-comment line-number">9.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> Orders <span class="hljs-keyword">values</span> <br><span class="hljs-comment line-number">10.</span>    -&gt; (<span class="hljs-literal">null</span>, <span class="hljs-string">'cpu'</span>, <span class="hljs-number">135.5</span> ,<span class="hljs-number">1</span>),<br><span class="hljs-comment line-number">11.</span>    -&gt; (<span class="hljs-literal">null</span>, <span class="hljs-string">'memory'</span>, <span class="hljs-number">48.2</span>, <span class="hljs-number">3</span>),<br><span class="hljs-comment line-number">12.</span>    -&gt; (<span class="hljs-literal">null</span>, <span class="hljs-string">'cpu'</span>, <span class="hljs-number">125.6</span>, <span class="hljs-number">3</span>),<br><span class="hljs-comment line-number">13.</span>    -&gt; (<span class="hljs-literal">null</span>, <span class="hljs-string">'cpu'</span>, <span class="hljs-number">105.3</span>, <span class="hljs-number">4</span>);</span><br><span class="hljs-comment line-number">14.</span>Query OK, 4 rows affected (0.06 sec)  <span class="hljs-comment">-- 插入测试数据</span><br><span class="hljs-comment line-number">15.</span>Records: 4  Duplicates: 0  Warnings: 0<br><span class="hljs-comment line-number">16.</span><br><span class="hljs-comment line-number">17.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span>  Orders;</span><br><span class="hljs-comment line-number">18.</span>+<span class="hljs-comment">----------+--------------+--------+--------+</span><br><span class="hljs-comment line-number">19.</span>| order_id | product_name | price  | amount |<br><span class="hljs-comment line-number">20.</span>+<span class="hljs-comment">----------+--------------+--------+--------+</span><br><span class="hljs-comment line-number">21.</span>|        1 | cpu          | 135.50 |      1 |<br><span class="hljs-comment line-number">22.</span>|        2 | memory       |  48.20 |      3 |<br><span class="hljs-comment line-number">23.</span>|        3 | cpu          | 125.60 |      3 |<br><span class="hljs-comment line-number">24.</span>|        4 | cpu          | 105.30 |      4 |<br><span class="hljs-comment line-number">25.</span>+<span class="hljs-comment">----------+--------------+--------+--------+</span><br><span class="hljs-comment line-number">26.</span>4 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">27.</span><br><span class="hljs-comment line-number">28.</span><span class="hljs-comment">-- 建立一个模拟物化视图的表（即用这张表来模拟物化视图）</span><br><span class="hljs-comment line-number">29.</span>mysql&gt; <span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> Orders_MV <br><span class="hljs-comment line-number">30.</span>    -&gt; ( product_name <span class="hljs-built_in">varchar</span>(<span class="hljs-number">30</span>) <span class="hljs-keyword">not</span> <span class="hljs-literal">null</span>,<br><span class="hljs-comment line-number">31.</span>    -&gt;  price_sum <span class="hljs-built_in">decimal</span>(<span class="hljs-number">8</span>,<span class="hljs-number">2</span>) <span class="hljs-keyword">not</span> <span class="hljs-literal">null</span>,<br><span class="hljs-comment line-number">32.</span>    -&gt;  amount_sum <span class="hljs-built_in">int</span> <span class="hljs-keyword">not</span> <span class="hljs-literal">null</span>,<br><span class="hljs-comment line-number">33.</span>    -&gt;  price_avg <span class="hljs-built_in">float</span> <span class="hljs-keyword">not</span> <span class="hljs-literal">null</span>,<br><span class="hljs-comment line-number">34.</span>    -&gt;  orders_cnt <span class="hljs-built_in">int</span> <span class="hljs-keyword">not</span> <span class="hljs-literal">null</span>,<br><span class="hljs-comment line-number">35.</span>    -&gt;  <span class="hljs-keyword">unique</span> <span class="hljs-keyword">index</span> (product_name));</span><br><span class="hljs-comment line-number">36.</span>Query OK, 0 rows affected (0.14 sec)<br><span class="hljs-comment line-number">37.</span><br><span class="hljs-comment line-number">38.</span><span class="hljs-comment">-- 通过Orders表的数据，将测试数据初始化到Orders_MV表中</span><br><span class="hljs-comment line-number">39.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> Orders_MV <br><span class="hljs-comment line-number">40.</span>    -&gt;     <span class="hljs-keyword">select</span> product_name, <span class="hljs-keyword">sum</span>(price), <br><span class="hljs-comment line-number">41.</span>    -&gt;            <span class="hljs-keyword">sum</span>(amount), <span class="hljs-keyword">avg</span>(price), <span class="hljs-keyword">count</span>(*)<br><span class="hljs-comment line-number">42.</span>    -&gt;     <span class="hljs-keyword">from</span> Orders<br><span class="hljs-comment line-number">43.</span>    -&gt;     <span class="hljs-keyword">group</span> <span class="hljs-keyword">by</span> product_name;</span><br><span class="hljs-comment line-number">44.</span>Query OK, 2 rows affected (0.07 sec)<br><span class="hljs-comment line-number">45.</span>Records: 2  Duplicates: 0  Warnings: 0<br><span class="hljs-comment line-number">46.</span><br><span class="hljs-comment line-number">47.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> Orders_MV;</span><br><span class="hljs-comment line-number">48.</span>+<span class="hljs-comment">--------------+-----------+------------+-----------+------------+</span><br><span class="hljs-comment line-number">49.</span>| product_name | price_sum | amount_sum | price_avg | orders_cnt |<br><span class="hljs-comment line-number">50.</span>+<span class="hljs-comment">--------------+-----------+------------+-----------+------------+</span><br><span class="hljs-comment line-number">51.</span>| cpu          |    366.40 |          8 |   122.133 |          3 |<br><span class="hljs-comment line-number">52.</span>| memory       |     48.20 |          3 |      48.2 |          1 |<br><span class="hljs-comment line-number">53.</span>+<span class="hljs-comment">--------------+-----------+------------+-----------+------------+</span><br><span class="hljs-comment line-number">54.</span>2 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">55.</span><br><span class="hljs-comment line-number">56.</span><span class="hljs-comment">-- 在MySQL workbench中输入，比较方便</span><br><span class="hljs-comment line-number">57.</span>delimiter //<br><span class="hljs-comment line-number">58.</span><br><span class="hljs-comment line-number">59.</span><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TRIGGER</span> tgr_Orders_insert <span class="hljs-comment">-- 创建触发器为tgr_Orders_insert</span><br><span class="hljs-comment line-number">60.</span>    <span class="hljs-keyword">AFTER</span> <span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">ON</span> Orders  <span class="hljs-comment">-- 触发器是INSERT类型的，且作用于Orders表</span><br><span class="hljs-comment line-number">61.</span>    <span class="hljs-keyword">FOR</span> <span class="hljs-keyword">EACH</span> <span class="hljs-keyword">ROW</span><br><span class="hljs-comment line-number">62.</span><span class="hljs-keyword">BEGIN</span><br><span class="hljs-comment line-number">63.</span>    <span class="hljs-keyword">SET</span> @old_price_sum := <span class="hljs-number">0</span>;</span>  <span class="hljs-comment">-- 设置临时存放Orders_MV表(模拟物化视图)的字段的变量</span><br><span class="hljs-comment line-number">64.</span>    <span class="hljs-operator"><span class="hljs-keyword">SET</span> @old_amount_sum := <span class="hljs-number">0</span>;</span><br><span class="hljs-comment line-number">65.</span>    <span class="hljs-operator"><span class="hljs-keyword">SET</span> @old_price_avg := <span class="hljs-number">0</span>;</span><br><span class="hljs-comment line-number">66.</span>    <span class="hljs-operator"><span class="hljs-keyword">SET</span> @old_orders_cnt := <span class="hljs-number">0</span>;</span><br><span class="hljs-comment line-number">67.</span>    <span class="hljs-operator"><span class="hljs-keyword">SELECT</span>   <span class="hljs-comment">-- select ... into ... 在更新Orders_MV之前，将Orders_MV中对应某个产品的信息写入临时变量 </span><br><span class="hljs-comment line-number">68.</span>        <span class="hljs-keyword">IFNULL</span>(price_sum, <span class="hljs-number">0</span>),<br><span class="hljs-comment line-number">69.</span>        <span class="hljs-keyword">IFNULL</span>(amount_sum, <span class="hljs-number">0</span>),<br><span class="hljs-comment line-number">70.</span>        <span class="hljs-keyword">IFNULL</span>(price_avg, <span class="hljs-number">0</span>),<br><span class="hljs-comment line-number">71.</span>        <span class="hljs-keyword">IFNULL</span>(orders_cnt, <span class="hljs-number">0</span>)<br><span class="hljs-comment line-number">72.</span>    <span class="hljs-keyword">FROM</span><br><span class="hljs-comment line-number">73.</span>        Orders_MV<br><span class="hljs-comment line-number">74.</span>    <span class="hljs-keyword">WHERE</span><br><span class="hljs-comment line-number">75.</span>        product_name = <span class="hljs-keyword">NEW</span>.product_name <span class="hljs-keyword">INTO</span> @old_price_sum , @old_amount_sum , @old_price_avg , @old_orders_cnt;</span><br><span class="hljs-comment line-number">76.</span><br><span class="hljs-comment line-number">77.</span>    <span class="hljs-operator"><span class="hljs-keyword">SET</span> @new_price_sum = @old_price_sum + <span class="hljs-keyword">NEW</span>.price;</span> <span class="hljs-comment">-- 累加新的值</span><br><span class="hljs-comment line-number">78.</span>    <span class="hljs-operator"><span class="hljs-keyword">SET</span> @new_amount_sum = @old_amount_sum + <span class="hljs-keyword">NEW</span>.amount;</span><br><span class="hljs-comment line-number">79.</span>    <span class="hljs-operator"><span class="hljs-keyword">SET</span> @new_orders_cnt = @old_orders_cnt + <span class="hljs-number">1</span>;</span><br><span class="hljs-comment line-number">80.</span>    <span class="hljs-operator"><span class="hljs-keyword">SET</span> @new_price_avg = @new_price_sum / @new_orders_cnt ;</span><br><span class="hljs-comment line-number">81.</span><br><span class="hljs-comment line-number">82.</span>    <span class="hljs-operator"><span class="hljs-keyword">REPLACE</span> <span class="hljs-keyword">INTO</span> Orders_MV   <br><span class="hljs-comment line-number">83.</span>            <span class="hljs-keyword">VALUES</span>(<span class="hljs-keyword">NEW</span>.product_name, @new_price_sum,<br><span class="hljs-comment line-number">84.</span>                   @new_amount_sum, @new_price_avg, @new_orders_cnt );</span><br><span class="hljs-comment line-number">85.</span>   <span class="hljs-comment">-- REPLACE 将对应的物品（唯一索引）的字段值替换new_xxx的值</span><br><span class="hljs-comment line-number">86.</span><span class="hljs-operator"><span class="hljs-keyword">END</span>;</span>//<br><span class="hljs-comment line-number">87.</span><br><span class="hljs-comment line-number">88.</span>delimiter ;<br><span class="hljs-comment line-number">89.</span><br><span class="hljs-comment line-number">90.</span><br><span class="hljs-comment line-number">91.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> Orders <span class="hljs-keyword">values</span> (<span class="hljs-literal">null</span>, <span class="hljs-string">'ssd'</span>, <span class="hljs-number">299</span>, <span class="hljs-number">3</span>);</span><br><span class="hljs-comment line-number">92.</span>Query OK, 1 row affected (0.03 sec)<br><span class="hljs-comment line-number">93.</span><br><span class="hljs-comment line-number">94.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> Orders <span class="hljs-keyword">values</span> (<span class="hljs-literal">null</span>, <span class="hljs-string">'memory'</span>, <span class="hljs-number">47.9</span>, <span class="hljs-number">5</span>);</span><br><span class="hljs-comment line-number">95.</span>Query OK, 1 row affected (0.05 sec)<br><span class="hljs-comment line-number">96.</span><br><span class="hljs-comment line-number">97.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> Orders_MV;</span><br><span class="hljs-comment line-number">98.</span>+<span class="hljs-comment">--------------+-----------+------------+-----------+------------+</span><br><span class="hljs-comment line-number">99.</span>| product_name | price_sum | amount_sum | price_avg | orders_cnt |<br><span class="hljs-comment line-number">100.</span>+<span class="hljs-comment">--------------+-----------+------------+-----------+------------+</span><br><span class="hljs-comment line-number">101.</span>| cpu          |    366.40 |          8 |   122.133 |          3 |<br><span class="hljs-comment line-number">102.</span>| memory       |     96.10 |          8 |     48.05 |          2 | <span class="hljs-comment">-- 数量自动增加了1，价格也发生了变化</span><br><span class="hljs-comment line-number">103.</span>| ssd          |    299.00 |          3 |       299 |          1 | <span class="hljs-comment">-- 新增加的ssd产品</span><br><span class="hljs-comment line-number">104.</span>+<span class="hljs-comment">--------------+-----------+------------+-----------+------------+</span><br><span class="hljs-comment line-number">105.</span>3 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">106.</span><br><span class="hljs-comment line-number">107.</span><br><span class="hljs-comment line-number">108.</span><span class="hljs-comment">--</span><br><span class="hljs-comment line-number">109.</span><span class="hljs-comment">-- IFNULL MySQL内建函数的演示</span><br><span class="hljs-comment line-number">110.</span><span class="hljs-comment">--</span><br><span class="hljs-comment line-number">111.</span>mysql&gt; <span class="hljs-keyword">select</span> @<span class="hljs-keyword">test</span>;</span><br><span class="hljs-comment line-number">112.</span>+<span class="hljs-comment">-------+</span><br><span class="hljs-comment line-number">113.</span>| @test |<br><span class="hljs-comment line-number">114.</span>+<span class="hljs-comment">-------+</span><br><span class="hljs-comment line-number">115.</span>| NULL  |  <span class="hljs-comment">-- 当前会话中没有test变量</span><br><span class="hljs-comment line-number">116.</span>+<span class="hljs-comment">-------+</span><br><span class="hljs-comment line-number">117.</span>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">118.</span><br><span class="hljs-comment line-number">119.</span>mysql&gt; <span class="hljs-keyword">select</span> <span class="hljs-keyword">ifnull</span>(@<span class="hljs-keyword">test</span>, <span class="hljs-number">100</span>);</span>   <span class="hljs-comment">-- 如果test为NULL，则ifnull返回100</span><br><span class="hljs-comment line-number">120.</span>+<span class="hljs-comment">--------------------+</span><br><span class="hljs-comment line-number">121.</span>| ifnull(@test, 100) |<br><span class="hljs-comment line-number">122.</span>+<span class="hljs-comment">--------------------+</span><br><span class="hljs-comment line-number">123.</span>| 100                |  <span class="hljs-comment">-- ifnull函数return的值是100</span><br><span class="hljs-comment line-number">124.</span>+<span class="hljs-comment">--------------------+</span><br><span class="hljs-comment line-number">125.</span>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">126.</span><br><span class="hljs-comment line-number">127.</span>mysql&gt; <span class="hljs-keyword">select</span> @<span class="hljs-keyword">test</span>;</span><br><span class="hljs-comment line-number">128.</span>+<span class="hljs-comment">-------+</span><br><span class="hljs-comment line-number">129.</span>| @test |<br><span class="hljs-comment line-number">130.</span>+<span class="hljs-comment">-------+</span><br><span class="hljs-comment line-number">131.</span>| NULL  |  <span class="hljs-comment">-- 但是test还是NULL</span><br><span class="hljs-comment line-number">132.</span>+<span class="hljs-comment">-------+</span><br><span class="hljs-comment line-number">133.</span>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">134.</span><br><span class="hljs-comment line-number">135.</span>mysql&gt; <span class="hljs-keyword">set</span> @<span class="hljs-keyword">test</span>:=<span class="hljs-number">200</span>;</span>  <span class="hljs-comment">-- 给test变量赋值为200</span><br><span class="hljs-comment line-number">136.</span>Query OK, 0 rows affected (0.00 sec)<br><span class="hljs-comment line-number">137.</span><br><span class="hljs-comment line-number">138.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> <span class="hljs-keyword">ifnull</span>(@<span class="hljs-keyword">test</span>, <span class="hljs-number">100</span>);</span>  <span class="hljs-comment">-- 再次ifnull判断，此时test不为null，则返回test变量的值</span><br><span class="hljs-comment line-number">139.</span>+<span class="hljs-comment">--------------------+</span><br><span class="hljs-comment line-number">140.</span>| ifnull(@test, 100) |<br><span class="hljs-comment line-number">141.</span>+<span class="hljs-comment">--------------------+</span><br><span class="hljs-comment line-number">142.</span>|                200 |  <span class="hljs-comment">-- test不为null。返回test的值200</span><br><span class="hljs-comment line-number">143.</span>+<span class="hljs-comment">--------------------+</span><br><span class="hljs-comment line-number">144.</span>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">145.</span><br><span class="hljs-comment line-number">146.</span><span class="hljs-comment">--</span><br><span class="hljs-comment line-number">147.</span><span class="hljs-comment">-- select into 用法</span><br><span class="hljs-comment line-number">148.</span><span class="hljs-comment">--</span><br><span class="hljs-comment line-number">149.</span>mysql&gt; <span class="hljs-keyword">select</span> @id_1;</span><br><span class="hljs-comment line-number">150.</span>+<span class="hljs-comment">-------+</span><br><span class="hljs-comment line-number">151.</span>| @id_1 |<br><span class="hljs-comment line-number">152.</span>+<span class="hljs-comment">-------+</span><br><span class="hljs-comment line-number">153.</span>|  NULL | <span class="hljs-comment">-- 当前变量id_1为null </span><br><span class="hljs-comment line-number">154.</span>+<span class="hljs-comment">-------+</span><br><span class="hljs-comment line-number">155.</span>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">156.</span><br><span class="hljs-comment line-number">157.</span>mysql&gt; <span class="hljs-keyword">select</span> @score_1;</span><br><span class="hljs-comment line-number">158.</span>+<span class="hljs-comment">----------+</span><br><span class="hljs-comment line-number">159.</span>| @score_1 |<br><span class="hljs-comment line-number">160.</span>+<span class="hljs-comment">----------+</span><br><span class="hljs-comment line-number">161.</span>|     NULL |  <span class="hljs-comment">-- 当前变量score_1为null</span><br><span class="hljs-comment line-number">162.</span>+<span class="hljs-comment">----------+</span><br><span class="hljs-comment line-number">163.</span>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">164.</span><br><span class="hljs-comment line-number">165.</span>mysql&gt; <span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> test_rank_2;</span><br><span class="hljs-comment line-number">166.</span>+<span class="hljs-comment">------+-------+</span><br><span class="hljs-comment line-number">167.</span>| id   | score |<br><span class="hljs-comment line-number">168.</span>+<span class="hljs-comment">------+-------+</span><br><span class="hljs-comment line-number">169.</span>|    1 |    10 |<br><span class="hljs-comment line-number">170.</span>|    2 |    20 |<br><span class="hljs-comment line-number">171.</span>|    3 |    30 |<br><span class="hljs-comment line-number">172.</span>|    4 |    30 |<br><span class="hljs-comment line-number">173.</span>|    5 |    40 |<br><span class="hljs-comment line-number">174.</span>|    6 |    40 |<br><span class="hljs-comment line-number">175.</span>+<span class="hljs-comment">------+-------+</span><br><span class="hljs-comment line-number">176.</span>6 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">177.</span><br><span class="hljs-comment line-number">178.</span>mysql&gt; <span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> test_rank_2 <br><span class="hljs-comment line-number">179.</span>    -&gt; <span class="hljs-keyword">where</span> <span class="hljs-keyword">id</span>=<span class="hljs-number">1</span> <span class="hljs-keyword">into</span> @id_1, @score_1;</span><br><span class="hljs-comment line-number">180.</span><span class="hljs-comment">-- 选择id=1的记录，将对应的id和score赋值给变量 id_1 和 score_1</span><br><span class="hljs-comment line-number">181.</span>Query OK, 1 row affected (0.00 sec)<br><span class="hljs-comment line-number">182.</span><br><span class="hljs-comment line-number">183.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> @id_1;</span><br><span class="hljs-comment line-number">184.</span>+<span class="hljs-comment">-------+</span><br><span class="hljs-comment line-number">185.</span>| @id_1 |<br><span class="hljs-comment line-number">186.</span>+<span class="hljs-comment">-------+</span><br><span class="hljs-comment line-number">187.</span>|     1 |<br><span class="hljs-comment line-number">188.</span>+<span class="hljs-comment">-------+</span><br><span class="hljs-comment line-number">189.</span>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">190.</span><br><span class="hljs-comment line-number">191.</span>mysql&gt; <span class="hljs-keyword">select</span> @score_1;</span><br><span class="hljs-comment line-number">192.</span>+<span class="hljs-comment">----------+</span><br><span class="hljs-comment line-number">193.</span>| @score_1 |<br><span class="hljs-comment line-number">194.</span>+<span class="hljs-comment">----------+</span><br><span class="hljs-comment line-number">195.</span>|       10 |<br><span class="hljs-comment line-number">196.</span>+<span class="hljs-comment">----------+</span><br><span class="hljs-comment line-number">197.</span>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">198.</span><br><span class="hljs-comment line-number">199.</span><span class="hljs-comment">-- 触发器对性能会有影响，相当于在一个事物中插入了其他的事物</span></span><br></code></pre>

<hr>

</div><div id="wmd-preview-section-11" class="wmd-preview-section preview-content">

<h2 id="三-存储过程">三. 存储过程</h2>

</div><div id="wmd-preview-section-12" class="wmd-preview-section preview-content">

<h3 id="1-存储过程介绍">1. 存储过程介绍</h3>

<ul><li>存储在数据库端的一组SQL语句集；</li>
<li>用户可以通过存储过程名和传参多次调用的程序模块；</li>
<li>存储过程的特点： <br>
<ul>
<li>使用灵活，可以使用流控语句、自定义变量等完成复杂的业务逻辑；</li>
<li>提高数据安全性，屏蔽应用程序直接对表的操作，易于进行审计；</li>
<li>减少网络传输；</li>
<li>提高代码维护的复杂度，实际使用需要结合业务评估；</li></ul></li>
</ul>

</div><div id="wmd-preview-section-13" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs"><span class="hljs-comment line-number">1.</span><span class="hljs-operator"><span class="hljs-keyword">CREATE</span><br><span class="hljs-comment line-number">2.</span>    [DEFINER = { <span class="hljs-keyword">user</span> | <span class="hljs-keyword">CURRENT_USER</span> }]<br><span class="hljs-comment line-number">3.</span>    <span class="hljs-keyword">PROCEDURE</span> sp_name ([proc_parameter[,...]])<br><span class="hljs-comment line-number">4.</span>    [characteristic ...] routine_body<br><span class="hljs-comment line-number">5.</span><br><span class="hljs-comment line-number">6.</span><br><span class="hljs-comment line-number">7.</span>proc_parameter:  <span class="hljs-comment">-- 注意，只有procedure才有in(传入),out(传出),inout(传入传出)参数，自定义函数（只有）默认就是 in。</span><br><span class="hljs-comment line-number">8.</span>    [ <span class="hljs-keyword">IN</span> | <span class="hljs-keyword">OUT</span> | INOUT ] param_name <span class="hljs-keyword">type</span> <br><span class="hljs-comment line-number">9.</span><br><span class="hljs-comment line-number">10.</span><br><span class="hljs-comment line-number">11.</span>characteristic:<br><span class="hljs-comment line-number">12.</span>    <span class="hljs-keyword">COMMENT</span> <span class="hljs-string">'string'</span><br><span class="hljs-comment line-number">13.</span>  | <span class="hljs-keyword">LANGUAGE</span> <span class="hljs-keyword">SQL</span><br><span class="hljs-comment line-number">14.</span>  | [<span class="hljs-keyword">NOT</span>] <span class="hljs-keyword">DETERMINISTIC</span><br><span class="hljs-comment line-number">15.</span>  | { CONTAINS <span class="hljs-keyword">SQL</span> | <span class="hljs-keyword">NO</span> <span class="hljs-keyword">SQL</span> | <span class="hljs-keyword">READS</span> <span class="hljs-keyword">SQL</span> <span class="hljs-keyword">DATA</span> | MODIFIES <span class="hljs-keyword">SQL</span> <span class="hljs-keyword">DATA</span> }<br><span class="hljs-comment line-number">16.</span>  | <span class="hljs-keyword">SQL</span> <span class="hljs-keyword">SECURITY</span> { DEFINER | INVOKER }<br><span class="hljs-comment line-number">17.</span><br><span class="hljs-comment line-number">18.</span>routine_body:<br><span class="hljs-comment line-number">19.</span>    Valid <span class="hljs-keyword">SQL</span> routine <span class="hljs-keyword">statement</span><br><span class="hljs-comment line-number">20.</span><br><span class="hljs-comment line-number">21.</span><br><span class="hljs-comment line-number">22.</span><span class="hljs-comment">-- 删除</span><br><span class="hljs-comment line-number">23.</span><span class="hljs-keyword">DROP</span> <span class="hljs-keyword">PROCEDURE</span>  procedure_name;</span><br></code></pre>

</div><div id="wmd-preview-section-14" class="wmd-preview-section preview-content">

<h3 id="2-存储过程举例与流程控制语句">2. 存储过程举例与流程控制语句</h3>

<blockquote>
  <p><a href="http://dev.mysql.com/doc/refman/5.7/en/flow-control-statements.html" target="_blank">流程控制语句 官方文档</a></p>
</blockquote>

</div><div id="wmd-preview-section-15" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs"><span class="hljs-comment line-number">1.</span><span class="hljs-comment">--</span><br><span class="hljs-comment line-number">2.</span><span class="hljs-comment">-- IF</span><br><span class="hljs-comment line-number">3.</span><span class="hljs-comment">--</span><br><span class="hljs-comment line-number">4.</span><span class="hljs-comment">-- 语法</span><br><span class="hljs-comment line-number">5.</span>IF search_condition THEN statement_list<br><span class="hljs-comment line-number">6.</span>    [ELSEIF search_condition THEN statement_list] ...<br><span class="hljs-comment line-number">7.</span>    [ELSE statement_list]<br><span class="hljs-comment line-number">8.</span><span class="hljs-operator"><span class="hljs-keyword">END</span> <span class="hljs-keyword">IF</span><br><span class="hljs-comment line-number">9.</span><br><span class="hljs-comment line-number">10.</span><span class="hljs-comment">-- 例子</span><br><span class="hljs-comment line-number">11.</span>mysql&gt; delimiter //<br><span class="hljs-comment line-number">12.</span>mysql&gt; <span class="hljs-keyword">create</span> <span class="hljs-keyword">procedure</span> pcd_test_1 (<span class="hljs-keyword">in</span> param_a <span class="hljs-built_in">int</span>) <span class="hljs-comment">-- 创建一个</span><br><span class="hljs-comment line-number">13.</span>    -&gt; <span class="hljs-keyword">begin</span><br><span class="hljs-comment line-number">14.</span>    -&gt; <span class="hljs-keyword">declare</span> a <span class="hljs-built_in">int</span>;</span>  <span class="hljs-comment">-- delcare声明了该变量的作用域在该procedure中</span><br><span class="hljs-comment line-number">15.</span>    -&gt;     if param_a &gt; 10 then <span class="hljs-operator"><span class="hljs-keyword">set</span> a:=<span class="hljs-number">11</span>;</span>        <br><span class="hljs-comment line-number">16.</span>    -&gt;         elseif param_a = 10 then <span class="hljs-operator"><span class="hljs-keyword">set</span> a:=<span class="hljs-number">10</span>;</span><br><span class="hljs-comment line-number">17.</span>    -&gt;         else <span class="hljs-operator"><span class="hljs-keyword">set</span> a:=<span class="hljs-number">9</span>;</span><br><span class="hljs-comment line-number">18.</span>    -&gt;     <span class="hljs-operator"><span class="hljs-keyword">end</span> <span class="hljs-keyword">if</span>;</span><br><span class="hljs-comment line-number">19.</span>    -&gt; <span class="hljs-operator"><span class="hljs-keyword">end</span>;</span>//<br><span class="hljs-comment line-number">20.</span>Query OK, 0 rows affected (0.01 sec)<br><span class="hljs-comment line-number">21.</span><br><span class="hljs-comment line-number">22.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> @a;</span>  <span class="hljs-comment">-- 查看当前会话中变量a的值</span><br><span class="hljs-comment line-number">23.</span>+<span class="hljs-comment">------+</span><br><span class="hljs-comment line-number">24.</span>| @a   |<br><span class="hljs-comment line-number">25.</span>+<span class="hljs-comment">------+</span><br><span class="hljs-comment line-number">26.</span>| NULL |  <span class="hljs-comment">-- 当前会话中a为NULL</span><br><span class="hljs-comment line-number">27.</span>+<span class="hljs-comment">------+</span><br><span class="hljs-comment line-number">28.</span>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">29.</span><br><span class="hljs-comment line-number">30.</span>mysql&gt; <span class="hljs-keyword">call</span> pcd_test_1(<span class="hljs-number">1</span>);</span><br><span class="hljs-comment line-number">31.</span>+<span class="hljs-comment">------+</span><br><span class="hljs-comment line-number">32.</span>| a    |<br><span class="hljs-comment line-number">33.</span>+<span class="hljs-comment">------+</span><br><span class="hljs-comment line-number">34.</span>|    9 |<br><span class="hljs-comment line-number">35.</span>+<span class="hljs-comment">------+</span><br><span class="hljs-comment line-number">36.</span>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">37.</span><br><span class="hljs-comment line-number">38.</span><span class="hljs-keyword">Query</span> OK, <span class="hljs-number">0</span> <span class="hljs-keyword">rows</span> affected (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">39.</span><br><span class="hljs-comment line-number">40.</span>mysql&gt; <span class="hljs-keyword">call</span> pcd_test_1(<span class="hljs-number">10</span>);</span><br><span class="hljs-comment line-number">41.</span>+<span class="hljs-comment">------+</span><br><span class="hljs-comment line-number">42.</span>| a    |<br><span class="hljs-comment line-number">43.</span>+<span class="hljs-comment">------+</span><br><span class="hljs-comment line-number">44.</span>|   10 |<br><span class="hljs-comment line-number">45.</span>+<span class="hljs-comment">------+</span><br><span class="hljs-comment line-number">46.</span>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">47.</span><br><span class="hljs-comment line-number">48.</span><span class="hljs-keyword">Query</span> OK, <span class="hljs-number">0</span> <span class="hljs-keyword">rows</span> affected (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">49.</span><br><span class="hljs-comment line-number">50.</span>mysql&gt; <span class="hljs-keyword">call</span> pcd_test_1(<span class="hljs-number">20</span>);</span><br><span class="hljs-comment line-number">51.</span>+<span class="hljs-comment">------+</span><br><span class="hljs-comment line-number">52.</span>| a    |<br><span class="hljs-comment line-number">53.</span>+<span class="hljs-comment">------+</span><br><span class="hljs-comment line-number">54.</span>|   11 |<br><span class="hljs-comment line-number">55.</span>+<span class="hljs-comment">------+</span><br><span class="hljs-comment line-number">56.</span>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">57.</span><br><span class="hljs-comment line-number">58.</span><span class="hljs-keyword">Query</span> OK, <span class="hljs-number">0</span> <span class="hljs-keyword">rows</span> affected (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">59.</span><br><span class="hljs-comment line-number">60.</span>mysql&gt; <span class="hljs-keyword">select</span> @a;</span><br><span class="hljs-comment line-number">61.</span>+<span class="hljs-comment">------+</span><br><span class="hljs-comment line-number">62.</span>| @a   |<br><span class="hljs-comment line-number">63.</span>+<span class="hljs-comment">------+</span><br><span class="hljs-comment line-number">64.</span>| NULL | <span class="hljs-comment">-- 使用了declare，使得procedure中a的作用域限制在了procedure内</span><br><span class="hljs-comment line-number">65.</span>+<span class="hljs-comment">------+</span><br><span class="hljs-comment line-number">66.</span>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">67.</span><br><span class="hljs-comment line-number">68.</span><span class="hljs-comment">--</span><br><span class="hljs-comment line-number">69.</span><span class="hljs-comment">-- CASE WHEN</span><br><span class="hljs-comment line-number">70.</span><span class="hljs-comment">--</span><br><span class="hljs-comment line-number">71.</span><span class="hljs-comment">-- CASE WHEN 语法</span><br><span class="hljs-comment line-number">72.</span><span class="hljs-keyword">CASE</span> case_value<br><span class="hljs-comment line-number">73.</span>    <span class="hljs-keyword">WHEN</span> when_value <span class="hljs-keyword">THEN</span> statement_list<br><span class="hljs-comment line-number">74.</span>    [<span class="hljs-keyword">WHEN</span> when_value <span class="hljs-keyword">THEN</span> statement_list] ...<br><span class="hljs-comment line-number">75.</span>    [<span class="hljs-keyword">ELSE</span> statement_list]<br><span class="hljs-comment line-number">76.</span><span class="hljs-keyword">END</span> <span class="hljs-keyword">CASE</span><br><span class="hljs-comment line-number">77.</span><span class="hljs-comment">-- 或者是</span><br><span class="hljs-comment line-number">78.</span><span class="hljs-keyword">CASE</span><br><span class="hljs-comment line-number">79.</span>    <span class="hljs-keyword">WHEN</span> search_condition <span class="hljs-keyword">THEN</span> statement_list<br><span class="hljs-comment line-number">80.</span>    [<span class="hljs-keyword">WHEN</span> search_condition <span class="hljs-keyword">THEN</span> statement_list] ...<br><span class="hljs-comment line-number">81.</span>    [<span class="hljs-keyword">ELSE</span> statement_list]<br><span class="hljs-comment line-number">82.</span><span class="hljs-keyword">END</span> <span class="hljs-keyword">CASE</span><br><span class="hljs-comment line-number">83.</span><br><span class="hljs-comment line-number">84.</span><span class="hljs-comment">--</span><br><span class="hljs-comment line-number">85.</span><span class="hljs-comment">-- CASE WHEN 例子</span><br><span class="hljs-comment line-number">86.</span><span class="hljs-comment">--</span><br><span class="hljs-comment line-number">87.</span>mysql&gt; delimiter //<br><span class="hljs-comment line-number">88.</span>mysql&gt; <br><span class="hljs-comment line-number">89.</span>mysql&gt; <span class="hljs-keyword">create</span> <span class="hljs-keyword">procedure</span> pcd_test_2(<span class="hljs-keyword">in</span> param_1 <span class="hljs-built_in">int</span>)<br><span class="hljs-comment line-number">90.</span>    -&gt;   <span class="hljs-keyword">begin</span><br><span class="hljs-comment line-number">91.</span>    -&gt;     <span class="hljs-keyword">case</span> param_1  <br><span class="hljs-comment line-number">92.</span>    <span class="hljs-comment">-- 当case后面有value时，该value会和when中的when_value进行"="判断</span><br><span class="hljs-comment line-number">93.</span>    <span class="hljs-comment">-- 相等则执行then后面的语句，然后跳出；否则就进行下一次when的匹配</span><br><span class="hljs-comment line-number">94.</span>    -&gt;       <span class="hljs-keyword">when</span> <span class="hljs-number">2</span> <span class="hljs-keyword">then</span> <span class="hljs-keyword">select</span> <span class="hljs-number">200</span>;</span><br><span class="hljs-comment line-number">95.</span>    -&gt;       when 3 then <span class="hljs-operator"><span class="hljs-keyword">select</span> <span class="hljs-number">300</span>;</span><br><span class="hljs-comment line-number">96.</span>    -&gt;       else<br><span class="hljs-comment line-number">97.</span>    -&gt;         <span class="hljs-operator"><span class="hljs-keyword">begin</span>  <br><span class="hljs-comment line-number">98.</span>                    <span class="hljs-comment">-- 当没有匹配时，且else中没有要执行的语句</span><br><span class="hljs-comment line-number">99.</span>                    <span class="hljs-comment">-- 则给一个begin/end的空语句；</span><br><span class="hljs-comment line-number">100.</span>                    <span class="hljs-comment">-- 或者不写else语句；</span><br><span class="hljs-comment line-number">101.</span>    -&gt;         <span class="hljs-keyword">end</span>;</span><br><span class="hljs-comment line-number">102.</span>    -&gt;     <span class="hljs-operator"><span class="hljs-keyword">end</span> <span class="hljs-keyword">case</span>;</span><br><span class="hljs-comment line-number">103.</span>    -&gt; <span class="hljs-operator"><span class="hljs-keyword">end</span>;</span>//<br><span class="hljs-comment line-number">104.</span>Query OK, 0 rows affected (0.03 sec)<br><span class="hljs-comment line-number">105.</span><br><span class="hljs-comment line-number">106.</span>mysql&gt; delimiter ;<br><span class="hljs-comment line-number">107.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">call</span> pcd_test_2(<span class="hljs-number">1</span>);</span><br><span class="hljs-comment line-number">108.</span>Query OK, 0 rows affected (0.00 sec)<br><span class="hljs-comment line-number">109.</span><br><span class="hljs-comment line-number">110.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">call</span> pcd_test_2(<span class="hljs-number">2</span>);</span><br><span class="hljs-comment line-number">111.</span>+<span class="hljs-comment">-----+</span><br><span class="hljs-comment line-number">112.</span>| 200 |<br><span class="hljs-comment line-number">113.</span>+<span class="hljs-comment">-----+</span><br><span class="hljs-comment line-number">114.</span>| 200 |<br><span class="hljs-comment line-number">115.</span>+<span class="hljs-comment">-----+</span><br><span class="hljs-comment line-number">116.</span>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">117.</span><br><span class="hljs-comment line-number">118.</span><span class="hljs-keyword">Query</span> OK, <span class="hljs-number">0</span> <span class="hljs-keyword">rows</span> affected (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">119.</span><br><span class="hljs-comment line-number">120.</span>mysql&gt; <span class="hljs-keyword">call</span> pcd_test_2(<span class="hljs-number">3</span>);</span><br><span class="hljs-comment line-number">121.</span>+<span class="hljs-comment">-----+</span><br><span class="hljs-comment line-number">122.</span>| 300 |<br><span class="hljs-comment line-number">123.</span>+<span class="hljs-comment">-----+</span><br><span class="hljs-comment line-number">124.</span>| 300 |<br><span class="hljs-comment line-number">125.</span>+<span class="hljs-comment">-----+</span><br><span class="hljs-comment line-number">126.</span>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">127.</span><br><span class="hljs-comment line-number">128.</span><span class="hljs-keyword">Query</span> OK, <span class="hljs-number">0</span> <span class="hljs-keyword">rows</span> affected (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">129.</span><br><span class="hljs-comment line-number">130.</span><span class="hljs-comment">-- 另外一种SQL语法请参考rank排名作业；注意when后跟的是condition</span><br><span class="hljs-comment line-number">131.</span><br><span class="hljs-comment line-number">132.</span><span class="hljs-comment">--</span><br><span class="hljs-comment line-number">133.</span><span class="hljs-comment">-- WHILE  循环</span><br><span class="hljs-comment line-number">134.</span><span class="hljs-comment">--</span><br><span class="hljs-comment line-number">135.</span><span class="hljs-comment">-- WHILE 语法</span><br><span class="hljs-comment line-number">136.</span>[begin_label:] <span class="hljs-keyword">WHILE</span> search_condition <span class="hljs-keyword">DO</span><br><span class="hljs-comment line-number">137.</span>    statement_list<br><span class="hljs-comment line-number">138.</span><span class="hljs-keyword">END</span> <span class="hljs-keyword">WHILE</span> [end_label]<br><span class="hljs-comment line-number">139.</span><br><span class="hljs-comment line-number">140.</span><span class="hljs-comment">-- WHILE举例</span><br><span class="hljs-comment line-number">141.</span>mysql&gt; delimiter //<br><span class="hljs-comment line-number">142.</span>mysql&gt; <br><span class="hljs-comment line-number">143.</span>mysql&gt; <span class="hljs-keyword">create</span> <span class="hljs-keyword">procedure</span> pcd_test_3(<span class="hljs-keyword">in</span> param_1 <span class="hljs-built_in">int</span>)<br><span class="hljs-comment line-number">144.</span>    -&gt; <span class="hljs-keyword">begin</span><br><span class="hljs-comment line-number">145.</span>    -&gt;     <span class="hljs-keyword">declare</span> a <span class="hljs-built_in">int</span> <span class="hljs-keyword">default</span> <span class="hljs-number">1</span>;</span><br><span class="hljs-comment line-number">146.</span>    -&gt;     while param_1 &gt; 10 <span class="hljs-operator"><span class="hljs-keyword">do</span><br><span class="hljs-comment line-number">147.</span>    -&gt;         <span class="hljs-keyword">set</span> param_1 = param_1 - <span class="hljs-number">1</span>;</span><br><span class="hljs-comment line-number">148.</span>    -&gt;         <span class="hljs-operator"><span class="hljs-keyword">set</span> a = a + <span class="hljs-number">1</span>;</span><br><span class="hljs-comment line-number">149.</span>    -&gt;     <span class="hljs-operator"><span class="hljs-keyword">end</span> <span class="hljs-keyword">while</span>;</span><br><span class="hljs-comment line-number">150.</span>    -&gt;     <span class="hljs-operator"><span class="hljs-keyword">select</span> a;</span><br><span class="hljs-comment line-number">151.</span>    -&gt; <span class="hljs-operator"><span class="hljs-keyword">end</span>;</span>//<br><span class="hljs-comment line-number">152.</span><br><span class="hljs-comment line-number">153.</span>Query OK, 0 rows affected (0.01 sec)<br><span class="hljs-comment line-number">154.</span>mysql&gt; delimiter ;<br><span class="hljs-comment line-number">155.</span><br><span class="hljs-comment line-number">156.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">call</span> pcd_test_3(<span class="hljs-number">15</span>);</span> <span class="hljs-comment">-- 15 - 10 = 5；需要5次循环</span><br><span class="hljs-comment line-number">157.</span>+<span class="hljs-comment">------+</span><br><span class="hljs-comment line-number">158.</span>| a    |<br><span class="hljs-comment line-number">159.</span>+<span class="hljs-comment">------+</span><br><span class="hljs-comment line-number">160.</span>|    6 |  <span class="hljs-comment">-- a + 5 = 6</span><br><span class="hljs-comment line-number">161.</span>+<span class="hljs-comment">------+</span><br><span class="hljs-comment line-number">162.</span>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">163.</span><br><span class="hljs-comment line-number">164.</span><span class="hljs-keyword">Query</span> OK, <span class="hljs-number">0</span> <span class="hljs-keyword">rows</span> affected (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">165.</span><br><span class="hljs-comment line-number">166.</span><br><span class="hljs-comment line-number">167.</span><span class="hljs-comment">--</span><br><span class="hljs-comment line-number">168.</span><span class="hljs-comment">-- REPEAT   循环</span><br><span class="hljs-comment line-number">169.</span><span class="hljs-comment">--</span><br><span class="hljs-comment line-number">170.</span><span class="hljs-comment">-- REPEAT 语法</span><br><span class="hljs-comment line-number">171.</span>[begin_label:] <span class="hljs-keyword">REPEAT</span><br><span class="hljs-comment line-number">172.</span>    statement_list<br><span class="hljs-comment line-number">173.</span><span class="hljs-keyword">UNTIL</span> search_condition<br><span class="hljs-comment line-number">174.</span><span class="hljs-keyword">END</span> <span class="hljs-keyword">REPEAT</span> [end_label]<br><span class="hljs-comment line-number">175.</span><br><span class="hljs-comment line-number">176.</span>mysql&gt; delimiter //<br><span class="hljs-comment line-number">177.</span>mysql&gt; <span class="hljs-keyword">create</span> <span class="hljs-keyword">procedure</span> pcd_test_4(<span class="hljs-keyword">in</span> param_1 <span class="hljs-built_in">int</span>)<br><span class="hljs-comment line-number">178.</span>    -&gt; <span class="hljs-keyword">begin</span><br><span class="hljs-comment line-number">179.</span>    -&gt;     <span class="hljs-keyword">SET</span> @x = <span class="hljs-number">0</span>;</span>  <span class="hljs-comment">-- 没有使用declare，所以x是会话级别的</span><br><span class="hljs-comment line-number">180.</span>    -&gt;     REPEAT<br><span class="hljs-comment line-number">181.</span>    -&gt;         <span class="hljs-operator"><span class="hljs-keyword">SET</span> @x = @x + <span class="hljs-number">1</span>;</span><br><span class="hljs-comment line-number">182.</span>    -&gt;     UNTIL @x &gt; param_1 <span class="hljs-operator"><span class="hljs-keyword">END</span> <span class="hljs-keyword">REPEAT</span>;</span><br><span class="hljs-comment line-number">183.</span>    -&gt; <span class="hljs-operator"><span class="hljs-keyword">end</span>;</span>//<br><span class="hljs-comment line-number">184.</span>Query OK, 0 rows affected (0.01 sec)<br><span class="hljs-comment line-number">185.</span>mysql&gt; delimiter ;<br><span class="hljs-comment line-number">186.</span><br><span class="hljs-comment line-number">187.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">call</span> pcd_test_4(<span class="hljs-number">10</span>);</span><br><span class="hljs-comment line-number">188.</span>Query OK, 0 rows affected (0.00 sec)<br><span class="hljs-comment line-number">189.</span><br><span class="hljs-comment line-number">190.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> @x;</span> <span class="hljs-comment">-- x是会话级别的</span><br><span class="hljs-comment line-number">191.</span>+<span class="hljs-comment">------+</span><br><span class="hljs-comment line-number">192.</span>| @x   |<br><span class="hljs-comment line-number">193.</span>+<span class="hljs-comment">------+</span><br><span class="hljs-comment line-number">194.</span>|   11 |  <span class="hljs-comment">-- 一共循环11次(10&gt;10 为False，11 &gt; 10为True，才跳出)</span><br><span class="hljs-comment line-number">195.</span>+<span class="hljs-comment">------+</span><br><span class="hljs-comment line-number">196.</span>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">197.</span><br><span class="hljs-comment line-number">198.</span><span class="hljs-comment">--</span><br><span class="hljs-comment line-number">199.</span><span class="hljs-comment">-- loop 循环</span><br><span class="hljs-comment line-number">200.</span><span class="hljs-comment">--</span><br><span class="hljs-comment line-number">201.</span><span class="hljs-comment">-- loop语法</span><br><span class="hljs-comment line-number">202.</span>[begin_label:] <span class="hljs-keyword">LOOP</span><br><span class="hljs-comment line-number">203.</span>    statement_list<br><span class="hljs-comment line-number">204.</span><span class="hljs-keyword">END</span> <span class="hljs-keyword">LOOP</span> [end_label]<br><span class="hljs-comment line-number">205.</span><br><span class="hljs-comment line-number">206.</span><span class="hljs-comment">-- ITERATE 和label相结合，表示继续从label处执行</span><br><span class="hljs-comment line-number">207.</span><span class="hljs-comment">-- LEAVE   和label相结合，表示从label 标记的代码段离开</span><br><span class="hljs-comment line-number">208.</span><br><span class="hljs-comment line-number">209.</span><span class="hljs-comment">-- loop 例子</span><br><span class="hljs-comment line-number">210.</span>mysql&gt; delimiter //<br><span class="hljs-comment line-number">211.</span>mysql&gt; <span class="hljs-keyword">create</span> <span class="hljs-keyword">procedure</span> pcd_test_5(<span class="hljs-keyword">in</span> param_1 <span class="hljs-built_in">int</span>)<br><span class="hljs-comment line-number">212.</span>    -&gt; <span class="hljs-keyword">begin</span><br><span class="hljs-comment line-number">213.</span>    -&gt;     test_label: <span class="hljs-keyword">loop</span><br><span class="hljs-comment line-number">214.</span>    -&gt;         <span class="hljs-keyword">set</span> param_1 := param_1 + <span class="hljs-number">1</span>;</span> <span class="hljs-comment">-- 参数累加</span><br><span class="hljs-comment line-number">215.</span>    -&gt;         if param_1 &lt; 10 then        <span class="hljs-comment">-- 如果累加的值小于10</span><br><span class="hljs-comment line-number">216.</span>    -&gt;             iterate test_label;     <span class="hljs-comment">-- 继续执行 标签 test_label</span><br><span class="hljs-comment line-number">217.</span>    -&gt;         <span class="hljs-operator"><span class="hljs-keyword">end</span> <span class="hljs-keyword">if</span>;</span><br><span class="hljs-comment line-number">218.</span>    -&gt;         leave test_label;  <span class="hljs-comment">-- 如果&gt;=10则离开这个test_label(loop)</span><br><span class="hljs-comment line-number">219.</span>    -&gt;     <span class="hljs-operator"><span class="hljs-keyword">end</span> <span class="hljs-keyword">loop</span> test_label;</span> <br><span class="hljs-comment line-number">220.</span>    -&gt;     <span class="hljs-operator"><span class="hljs-keyword">set</span> @x = param_1;</span> <span class="hljs-comment">-- 设置会话级别的变量 </span><br><span class="hljs-comment line-number">221.</span>    -&gt; <span class="hljs-operator"><span class="hljs-keyword">end</span>;</span>//<br><span class="hljs-comment line-number">222.</span><br><span class="hljs-comment line-number">223.</span>Query OK, 0 rows affected (0.02 sec)<br><span class="hljs-comment line-number">224.</span>mysql&gt; delimiter ;<br><span class="hljs-comment line-number">225.</span><br><span class="hljs-comment line-number">226.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">call</span> pcd_test_5(<span class="hljs-number">5</span>);</span> <span class="hljs-comment">-- 5&lt;10 ，累加5次后&gt;=10为true，离开循环</span><br><span class="hljs-comment line-number">227.</span>Query OK, 0 rows affected (0.00 sec)<br><span class="hljs-comment line-number">228.</span><br><span class="hljs-comment line-number">229.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> @x;</span><br><span class="hljs-comment line-number">230.</span>+<span class="hljs-comment">------+</span><br><span class="hljs-comment line-number">231.</span>| @x   |<br><span class="hljs-comment line-number">232.</span>+<span class="hljs-comment">------+</span><br><span class="hljs-comment line-number">233.</span>|   10 |  <span class="hljs-comment">-- 累加到10的 param_1 赋值给 x, 即为10</span><br><span class="hljs-comment line-number">234.</span>+<span class="hljs-comment">------+</span><br><span class="hljs-comment line-number">235.</span>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">236.</span><br><span class="hljs-comment line-number">237.</span><br><span class="hljs-comment line-number">238.</span><span class="hljs-comment">-- 老师给出的例子， 阶乘</span><br><span class="hljs-comment line-number">239.</span>mysql&gt; <span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> test_proc_1(a <span class="hljs-built_in">int</span>, b <span class="hljs-built_in">int</span>);</span> <span class="hljs-comment">-- 给一个存放数据的表</span><br><span class="hljs-comment line-number">240.</span>Query OK, 0 rows affected (0.15 sec)<br><span class="hljs-comment line-number">241.</span><br><span class="hljs-comment line-number">242.</span>mysql&gt; delimiter //<br><span class="hljs-comment line-number">243.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">create</span> <span class="hljs-keyword">procedure</span> proc_test1(<span class="hljs-keyword">in</span> total <span class="hljs-built_in">int</span>, <span class="hljs-keyword">out</span> res <span class="hljs-built_in">int</span>)<br><span class="hljs-comment line-number">244.</span>    -&gt; <span class="hljs-keyword">begin</span><br><span class="hljs-comment line-number">245.</span>    -&gt;     <span class="hljs-keyword">declare</span> <span class="hljs-keyword">i</span> <span class="hljs-built_in">int</span>;</span><br><span class="hljs-comment line-number">246.</span>    -&gt;     <span class="hljs-operator"><span class="hljs-keyword">set</span> <span class="hljs-keyword">i</span> := <span class="hljs-number">1</span>;</span><br><span class="hljs-comment line-number">247.</span>    -&gt;     <span class="hljs-operator"><span class="hljs-keyword">set</span> res := <span class="hljs-number">1</span>;</span><br><span class="hljs-comment line-number">248.</span>    -&gt;     if total &lt;= 0 then<br><span class="hljs-comment line-number">249.</span>    -&gt;         <span class="hljs-operator"><span class="hljs-keyword">set</span> total := <span class="hljs-number">1</span>;</span><br><span class="hljs-comment line-number">250.</span>    -&gt;     <span class="hljs-operator"><span class="hljs-keyword">end</span> <span class="hljs-keyword">if</span>;</span><br><span class="hljs-comment line-number">251.</span>    -&gt;     while i &lt;= total <span class="hljs-operator"><span class="hljs-keyword">do</span><br><span class="hljs-comment line-number">252.</span>    -&gt;         <span class="hljs-keyword">set</span> res := res * <span class="hljs-keyword">i</span>;</span><br><span class="hljs-comment line-number">253.</span>    -&gt;         <span class="hljs-operator"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> test_proc_1 <span class="hljs-keyword">values</span>(<span class="hljs-keyword">i</span>, res);</span><br><span class="hljs-comment line-number">254.</span>    -&gt;         <span class="hljs-operator"><span class="hljs-keyword">set</span> <span class="hljs-keyword">i</span> := <span class="hljs-keyword">i</span> + <span class="hljs-number">1</span>;</span><br><span class="hljs-comment line-number">255.</span>    -&gt;     <span class="hljs-operator"><span class="hljs-keyword">end</span> <span class="hljs-keyword">while</span>;</span><br><span class="hljs-comment line-number">256.</span>    -&gt; <span class="hljs-operator"><span class="hljs-keyword">end</span>;</span>//<br><span class="hljs-comment line-number">257.</span>Query OK, 0 rows affected (0.01 sec)<br><span class="hljs-comment line-number">258.</span>mysql&gt; delimiter ;<br><span class="hljs-comment line-number">259.</span><br><span class="hljs-comment line-number">260.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">set</span> @res_value := <span class="hljs-number">0</span>;</span><br><span class="hljs-comment line-number">261.</span>Query OK, 0 rows affected (0.00 sec)<br><span class="hljs-comment line-number">262.</span><br><span class="hljs-comment line-number">263.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">call</span> proc_test1(<span class="hljs-number">5</span>, @res_value);</span> <span class="hljs-comment">-- 因为res是out变量，要预先有这个变量，这里上面设置了res_value(实参和形参不必同名)</span><br><span class="hljs-comment line-number">264.</span>Query OK, 1 row affected (0.15 sec)<br><span class="hljs-comment line-number">265.</span><br><span class="hljs-comment line-number">266.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> @res_value;</span><br><span class="hljs-comment line-number">267.</span>+<span class="hljs-comment">------------+</span><br><span class="hljs-comment line-number">268.</span>| @res_value |<br><span class="hljs-comment line-number">269.</span>+<span class="hljs-comment">------------+</span><br><span class="hljs-comment line-number">270.</span>|        120 | <span class="hljs-comment">-- 5的阶乘的结果是120</span><br><span class="hljs-comment line-number">271.</span>+<span class="hljs-comment">------------+</span><br><span class="hljs-comment line-number">272.</span>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">273.</span><br><span class="hljs-comment line-number">274.</span>mysql&gt; <span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> test_proc_1;</span><br><span class="hljs-comment line-number">275.</span>+<span class="hljs-comment">------+------+</span><br><span class="hljs-comment line-number">276.</span>| a    | b    |<br><span class="hljs-comment line-number">277.</span>+<span class="hljs-comment">------+------+</span><br><span class="hljs-comment line-number">278.</span>|    1 |    1 |<br><span class="hljs-comment line-number">279.</span>|    2 |    2 |<br><span class="hljs-comment line-number">280.</span>|    3 |    6 |<br><span class="hljs-comment line-number">281.</span>|    4 |   24 |<br><span class="hljs-comment line-number">282.</span>|    5 |  120 |  <span class="hljs-comment">-- 每次insert的结果</span><br><span class="hljs-comment line-number">283.</span>+<span class="hljs-comment">------+------+</span><br><span class="hljs-comment line-number">284.</span>5 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)</span><br></code></pre>

<hr>

</div><div id="wmd-preview-section-16" class="wmd-preview-section preview-content">

<h2 id="三-自定义函数">三. 自定义函数</h2>

<ul><li>自定义函数和存储过程很类似，但是必须要有返回值；</li>
<li>与内置的函数(sum(), max()等)使用方法类似 <br>
<ul>
<li>select fun(val);</li>
<li>select * from t where  col= fun(val);</li></ul></li>
<li>自定义函数可能在遍历每条记录中使用；</li>
</ul>

</div><div id="wmd-preview-section-17" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs"><span class="hljs-comment line-number">1.</span><span class="hljs-operator"><span class="hljs-keyword">CREATE</span><br><span class="hljs-comment line-number">2.</span>    [DEFINER = { <span class="hljs-keyword">user</span> | <span class="hljs-keyword">CURRENT_USER</span> }]<br><span class="hljs-comment line-number">3.</span>    <span class="hljs-keyword">FUNCTION</span> sp_name ([func_parameter[,...]])<br><span class="hljs-comment line-number">4.</span>    <span class="hljs-keyword">RETURNS</span> <span class="hljs-keyword">type</span>  <span class="hljs-comment">-- 必须有返回值</span><br><span class="hljs-comment line-number">5.</span>    [characteristic ...] routine_body<br><span class="hljs-comment line-number">6.</span><br><span class="hljs-comment line-number">7.</span>func_parameter:<br><span class="hljs-comment line-number">8.</span>    param_name <span class="hljs-keyword">type</span><br><span class="hljs-comment line-number">9.</span><br><span class="hljs-comment line-number">10.</span><span class="hljs-keyword">type</span>:<br><span class="hljs-comment line-number">11.</span>    <span class="hljs-keyword">Any</span> valid MySQL <span class="hljs-keyword">data</span> <span class="hljs-keyword">type</span><br><span class="hljs-comment line-number">12.</span><br><span class="hljs-comment line-number">13.</span>characteristic:<br><span class="hljs-comment line-number">14.</span>    <span class="hljs-keyword">COMMENT</span> <span class="hljs-string">'string'</span><br><span class="hljs-comment line-number">15.</span>  | <span class="hljs-keyword">LANGUAGE</span> <span class="hljs-keyword">SQL</span><br><span class="hljs-comment line-number">16.</span>  | [<span class="hljs-keyword">NOT</span>] <span class="hljs-keyword">DETERMINISTIC</span><br><span class="hljs-comment line-number">17.</span>  | { CONTAINS <span class="hljs-keyword">SQL</span> | <span class="hljs-keyword">NO</span> <span class="hljs-keyword">SQL</span> | <span class="hljs-keyword">READS</span> <span class="hljs-keyword">SQL</span> <span class="hljs-keyword">DATA</span> | MODIFIES <span class="hljs-keyword">SQL</span> <span class="hljs-keyword">DATA</span> }<br><span class="hljs-comment line-number">18.</span>  | <span class="hljs-keyword">SQL</span> <span class="hljs-keyword">SECURITY</span> { DEFINER | INVOKER }<br><span class="hljs-comment line-number">19.</span><br><span class="hljs-comment line-number">20.</span>routine_body:<br><span class="hljs-comment line-number">21.</span>    Valid <span class="hljs-keyword">SQL</span> routine <span class="hljs-keyword">statement</span><br><span class="hljs-comment line-number">22.</span><br><span class="hljs-comment line-number">23.</span><span class="hljs-comment">-- 删除</span><br><span class="hljs-comment line-number">24.</span><span class="hljs-keyword">DROP</span> <span class="hljs-keyword">FUNCTION</span> fun_name;</span><br></code></pre>

</div><div id="wmd-preview-section-5130" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs"><span class="hljs-comment line-number">1.</span><span class="hljs-comment">-- 老师给的例子，还是阶乘，用自定义函数的方式</span><br><span class="hljs-comment line-number">2.</span><br><span class="hljs-comment line-number">3.</span>mysql&gt; delimiter //<br><span class="hljs-comment line-number">4.</span>mysql&gt; <br><span class="hljs-comment line-number">5.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">create</span> <span class="hljs-keyword">function</span> fun_test_1(total <span class="hljs-built_in">int</span>)<br><span class="hljs-comment line-number">6.</span>    -&gt; <span class="hljs-keyword">returns</span> <span class="hljs-built_in">int</span><br><span class="hljs-comment line-number">7.</span>    -&gt; <span class="hljs-keyword">begin</span><br><span class="hljs-comment line-number">8.</span>    -&gt;     <span class="hljs-keyword">declare</span> <span class="hljs-keyword">i</span> <span class="hljs-built_in">int</span>;</span><br><span class="hljs-comment line-number">9.</span>    -&gt;     <span class="hljs-operator"><span class="hljs-keyword">declare</span> res <span class="hljs-built_in">int</span>;</span><br><span class="hljs-comment line-number">10.</span>    -&gt;     <span class="hljs-operator"><span class="hljs-keyword">set</span> <span class="hljs-keyword">i</span> := <span class="hljs-number">1</span>;</span><br><span class="hljs-comment line-number">11.</span>    -&gt;     <span class="hljs-operator"><span class="hljs-keyword">set</span> res := <span class="hljs-number">1</span>;</span><br><span class="hljs-comment line-number">12.</span>    -&gt;     if total &lt;= 0 then<br><span class="hljs-comment line-number">13.</span>    -&gt;         <span class="hljs-operator"><span class="hljs-keyword">set</span> total := <span class="hljs-number">1</span>;</span><br><span class="hljs-comment line-number">14.</span>    -&gt; <span class="hljs-operator"><span class="hljs-keyword">end</span> <span class="hljs-keyword">if</span>;</span><br><span class="hljs-comment line-number">15.</span>    -&gt;     while i &lt;= total <span class="hljs-operator"><span class="hljs-keyword">do</span><br><span class="hljs-comment line-number">16.</span>    -&gt;         <span class="hljs-keyword">set</span> res := res * <span class="hljs-keyword">i</span>;</span><br><span class="hljs-comment line-number">17.</span>    -&gt;         <span class="hljs-operator"><span class="hljs-keyword">set</span> <span class="hljs-keyword">i</span> := <span class="hljs-keyword">i</span> + <span class="hljs-number">1</span>;</span><br><span class="hljs-comment line-number">18.</span>    -&gt;     <span class="hljs-operator"><span class="hljs-keyword">end</span> <span class="hljs-keyword">while</span>;</span><br><span class="hljs-comment line-number">19.</span>    -&gt;     return res;<br><span class="hljs-comment line-number">20.</span>    -&gt; <span class="hljs-operator"><span class="hljs-keyword">end</span>;</span>//<br><span class="hljs-comment line-number">21.</span>ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to <span class="hljs-operator"><span class="hljs-keyword">use</span> the <span class="hljs-keyword">less</span> <span class="hljs-keyword">safe</span> log_bin_trust_function_creators <span class="hljs-keyword">variable</span>)<br><span class="hljs-comment line-number">22.</span><span class="hljs-comment">-- 报错，提示因为函的声明中没有"DETERMINISTIC, NO SQL, or READS SQL DATA"等关键字 ，需要使用打开参数 log_bin_trust_function_creators</span><br><span class="hljs-comment line-number">23.</span><br><span class="hljs-comment line-number">24.</span><span class="hljs-comment">-- 解决方法，set global log_bin_trust_function_creators=1; 开启该选项可能会引起主从服务器不一致</span><br><span class="hljs-comment line-number">25.</span><span class="hljs-comment">--           或者 增加 上述相应功能的关键字</span><br><span class="hljs-comment line-number">26.</span><br><span class="hljs-comment line-number">27.</span><br><span class="hljs-comment line-number">28.</span><span class="hljs-comment">-- 使用 deterministic 关键字</span><br><span class="hljs-comment line-number">29.</span><span class="hljs-comment">-- 当你声明一个函数的返回是确定性的，则必须显示的使用deterministic关键字，默认是 no deterministic的</span><br><span class="hljs-comment line-number">30.</span>mysql&gt; delimiter //<br><span class="hljs-comment line-number">31.</span>mysql&gt; <span class="hljs-keyword">create</span> <span class="hljs-keyword">function</span> fun_test_1(total <span class="hljs-built_in">int</span>)<br><span class="hljs-comment line-number">32.</span>    -&gt; <span class="hljs-keyword">returns</span> <span class="hljs-built_in">int</span> <span class="hljs-keyword">deterministic</span> <span class="hljs-comment">-- 这个只是告诉MySQL我这个函数是否会改变数据</span><br><span class="hljs-comment line-number">33.</span>                                 <span class="hljs-comment">-- 即使我下面使用了insert，update等DML语句，MySQL不会检查</span><br><span class="hljs-comment line-number">34.</span>                                 <span class="hljs-comment">-- 函数是否会改变数据，完全依赖创建函数的用户去指定的关键字</span><br><span class="hljs-comment line-number">35.</span>                                 <span class="hljs-comment">-- 而非真的是否有修改数据</span><br><span class="hljs-comment line-number">36.</span>                                 <span class="hljs-comment">-- 只是声明，而非约束</span><br><span class="hljs-comment line-number">37.</span>    -&gt; <span class="hljs-keyword">begin</span><br><span class="hljs-comment line-number">38.</span>    -&gt;     <span class="hljs-keyword">declare</span> <span class="hljs-keyword">i</span> <span class="hljs-built_in">int</span>;</span><br><span class="hljs-comment line-number">39.</span>    -&gt;     <span class="hljs-operator"><span class="hljs-keyword">declare</span> res <span class="hljs-built_in">int</span>;</span><br><span class="hljs-comment line-number">40.</span>    -&gt;     <span class="hljs-operator"><span class="hljs-keyword">set</span> <span class="hljs-keyword">i</span> := <span class="hljs-number">1</span>;</span><br><span class="hljs-comment line-number">41.</span>    -&gt;     <span class="hljs-operator"><span class="hljs-keyword">set</span> res := <span class="hljs-number">1</span>;</span><br><span class="hljs-comment line-number">42.</span>    -&gt;     if total &lt;= 0 then<br><span class="hljs-comment line-number">43.</span>    -&gt;         <span class="hljs-operator"><span class="hljs-keyword">set</span> total := <span class="hljs-number">1</span>;</span><br><span class="hljs-comment line-number">44.</span>    -&gt;     <span class="hljs-operator"><span class="hljs-keyword">end</span> <span class="hljs-keyword">if</span>;</span><br><span class="hljs-comment line-number">45.</span>    -&gt;     while i &lt;= total <span class="hljs-operator"><span class="hljs-keyword">do</span><br><span class="hljs-comment line-number">46.</span>    -&gt;         <span class="hljs-keyword">set</span> res := res * <span class="hljs-keyword">i</span>;</span><br><span class="hljs-comment line-number">47.</span>    -&gt;         <span class="hljs-operator"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> test_proc_1 <span class="hljs-keyword">values</span>(<span class="hljs-keyword">i</span>, res);</span>  <span class="hljs-comment">-- 在自定义函数中，同样可以使用sql</span><br><span class="hljs-comment line-number">48.</span>                                                        <span class="hljs-comment">-- 并且该SQL是insert，其实和deterministic违背。</span><br><span class="hljs-comment line-number">49.</span>    -&gt;         <span class="hljs-operator"><span class="hljs-keyword">set</span> <span class="hljs-keyword">i</span> := <span class="hljs-keyword">i</span> + <span class="hljs-number">1</span>;</span><br><span class="hljs-comment line-number">50.</span>    -&gt;     <span class="hljs-operator"><span class="hljs-keyword">end</span> <span class="hljs-keyword">while</span>;</span><br><span class="hljs-comment line-number">51.</span>    -&gt;     return res;<br><span class="hljs-comment line-number">52.</span>    -&gt; <span class="hljs-operator"><span class="hljs-keyword">end</span>;</span>//<br><span class="hljs-comment line-number">53.</span>Query OK, 0 rows affected (0.01 sec)<br><span class="hljs-comment line-number">54.</span>mysql&gt; delimiter ;<br><span class="hljs-comment line-number">55.</span><br><span class="hljs-comment line-number">56.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">truncate</span> <span class="hljs-keyword">table</span> test_proc_1;</span><br><span class="hljs-comment line-number">57.</span>Query OK, 0 rows affected (0.11 sec)<br><span class="hljs-comment line-number">58.</span><br><span class="hljs-comment line-number">59.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> fun_test_1(<span class="hljs-number">6</span>);</span>  <span class="hljs-comment">-- return了6的阶乘，720</span><br><span class="hljs-comment line-number">60.</span>+<span class="hljs-comment">---------------+</span><br><span class="hljs-comment line-number">61.</span>| fun_test_1(6) |<br><span class="hljs-comment line-number">62.</span>+<span class="hljs-comment">---------------+</span><br><span class="hljs-comment line-number">63.</span>|           720 |<br><span class="hljs-comment line-number">64.</span>+<span class="hljs-comment">---------------+</span><br><span class="hljs-comment line-number">65.</span>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.02</span> sec)<br><span class="hljs-comment line-number">66.</span><br><span class="hljs-comment line-number">67.</span>mysql&gt; <span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> test_proc_1;</span> <br><span class="hljs-comment line-number">68.</span>+<span class="hljs-comment">------+------+</span><br><span class="hljs-comment line-number">69.</span>| a    | b    |<br><span class="hljs-comment line-number">70.</span>+<span class="hljs-comment">------+------+</span><br><span class="hljs-comment line-number">71.</span>|    1 |    1 |<br><span class="hljs-comment line-number">72.</span>|    2 |    2 |<br><span class="hljs-comment line-number">73.</span>|    3 |    6 |<br><span class="hljs-comment line-number">74.</span>|    4 |   24 |<br><span class="hljs-comment line-number">75.</span>|    5 |  120 |<br><span class="hljs-comment line-number">76.</span>|    6 |  720 |  <span class="hljs-comment">-- 使用了insert语句进行插入阶乘的历史记录</span><br><span class="hljs-comment line-number">77.</span>+<span class="hljs-comment">------+------+</span><br><span class="hljs-comment line-number">78.</span>6 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">79.</span><br><span class="hljs-comment line-number">80.</span><span class="hljs-comment">-- 关键字简单说明</span><br><span class="hljs-comment line-number">81.</span><span class="hljs-comment">-- DETERMINISTIC ： 当给定相同的输入，产生确定的结果</span><br><span class="hljs-comment line-number">82.</span><span class="hljs-comment">-- NOT DETERMINISTIC ： 默认值，认为产生的结果是不确定的</span><br><span class="hljs-comment line-number">83.</span><br><span class="hljs-comment line-number">84.</span><span class="hljs-comment">-- READS SQL DATA  ： 只是读取SQL数据</span><br><span class="hljs-comment line-number">85.</span><span class="hljs-comment">-- MODIFIES SQL DATA ： 会修改数据</span><br><span class="hljs-comment line-number">86.</span><span class="hljs-comment">-- NO SQL ： 没有SQL遇见</span><br><span class="hljs-comment line-number">87.</span><span class="hljs-comment">-- CONTAINS SQL ： 包含SQL语句，但是没有读写语句，理论有select now()等</span><br><span class="hljs-comment line-number">88.</span></span><br></code></pre>

<p><a href="https://dev.mysql.com/doc/refman/5.7/en/stored-programs-logging.html" target="_blank">原文链接</a> <br>
<strong>部分原文：</strong> <br>
<em>By default, for a CREATE FUNCTION statement to be accepted, at least one of DETERMINISTIC, NO SQL, or READS SQL DATA must be specified explicitly. Otherwise an error occurs:</em> <br>
默认情况下，在创建自定义函数时，必须显示的指定关键字<code>DETERMINISTIC, NO SQL, 或者是 READS SQL DATA</code>中的至少一个(可以多个)，否则就会有如下错误</p>

<blockquote>
  <p>ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL,or READS SQL DATA in its declaration and binary logging is enabled (you <em>might</em> want to use the less safe log_bin_trust_function_creators <br>
  variable)</p>
</blockquote>

<p><a href="http://stackoverflow.com/questions/26015160/deterministic-no-sql-or-reads-sql-data-in-its-declaration-and-binary-logging-i" target="_blank">网上参考资料1</a> <br>
<a href="http://blog.csdn.net/melody_mr/article/details/43529719" target="_blank">网上参考资料2</a></p></div><div id="wmd-preview-section-footnotes" class="preview-content"></div></div></body></html>